-- Add more options for repeat patterns
-- Add 6 month repeat
update repeatPattern set displaySequence = 9 where uidRepeatPattern = 7;
insert into repeatPattern values (9, '6MONTH', 'Every six months', '+6 months', 8);

-- New balance sheet
update accountSubCategory 
   set uidBsCategory=2 
 where uidBsCategory=3;

delete from bsCategory 
 where uidBsCategory=3;

update bsCategory
   set description = 'Current Assets & Liabilities'
 where uidBsCategory=2;

-- Fix for accruals incorrectly placed as fixed liabilities
update accountSubCategory 
   set uidBsCategory=2 
 where uidAccountSubCat=19;

update accountSubCategory 
   set creditText = 'Payment / Credit'
 where uidAccountSubCat = 11;

update accountSubCategory 
   set debitText = 'Payment / Credit'
 where uidAccountSubCat = 12;

update accountSubCategory 
   set debitText = 'Loss',
       creditText = 'Gain'
 where uidAccountSubCat in (7,8,9);

-- Create tables for 'products'
CREATE TABLE product (
  uidProduct integer PRIMARY KEY,
  productName varchar not null,
  description varchar not null,
  uidAccountIncome integer not null default 0,
  uidAccountAsset integer not null default 0,   -- Zero for sales of services (no qty to adjust in stock)
  uidAccountExpense integer not null default 0, -- Zero for sales of services (no qty to adjust in stock)
  isActive integer not null default 1,
  isContainer integer not null default 0,
  learnPrices integer not null default 1,
  openingQty real not null default 0,
  openingValuation integer not null default 0,
  journalQty real not null default 0,
  journalValuation integer not null default 0
);
CREATE INDEX ix_product_income on product(uidAccountIncome);
CREATE INDEX ix_product_asset on product(uidAccountAsset);
CREATE INDEX ix_product_expense on product(uidAccountExpense);
CREATE INDEX uk_product_income_name on product(uidAccountIncome, productName);

create table bom (
  uidBom integer PRIMARY KEY,
  uidProduct integer not null,
  uidProductPart integer not null,
  qty real not null default 1
);
CREATE INDEX ix_bom_product on bom(uidProduct);
CREATE INDEX ix_bom_productPart on bom(uidProductPart);

-- Add the product tracking columns on each split
alter table journalLine add column uidProduct integer not null default 0;
alter table journalLine add column qty real not null default 0;
alter table journalLine add column assetDirection integer not null default 0;
alter table journalLine add column costAmount integer not null default 0;

alter table journal add column direction integer not null default 0;

CREATE INDEX ix_journalLine_product on journalLine(uidProduct);

-- Add the product tracking columns on each estimate split
alter table estimateLine add column uidProduct integer not null default 0;
alter table estimateLine add column qty real not null default 0;

-- Asset and Expense tags
INSERT INTO accountSubCatTagRel (uidAccountSubCatTagRel, uidAccountSubCat, uidTag)
          SELECT 137,13,17
UNION ALL SELECT 138,16,17
UNION ALL SELECT 139,4,18;

-- The relation is for the other entries linked to the base entry.
-- E.g. 'PAYMENT' would be a relation for a sale uidJournal when
-- the other journal entry is a payment.
alter table payment add column relationType integer not null default 1;
drop index ix_payment_invoice;
create index ix_payment_invoice on payment (uidJournalInvoice, relationType);

CREATE TABLE productPrice (
  uidProductPrice integer PRIMARY KEY,
  uidProduct integer not null,
  qty real not null,
  each real not null,
  learned integer not null
);
CREATE INDEX uk_productPrice on productPrice(uidProduct, qty);

-- Metadata
delete from tablemetadata where uidTable in (5,6);
delete from columnmetadata where uidTable in (5,6);

INSERT INTO columnTypeMetadata VALUES(4,'real');

INSERT INTO tablemetadata (uidTable, tableName, canSync, generation)
          SELECT 24,'product',1,3
UNION ALL SELECT 25,'bom',1,4
UNION ALL SELECT 27,'productPrice',1,4;

INSERT INTO columnmetadata (uidColumn, uidTable, columnNumber, columnName, uidColumnType, isPrimaryKey, canSync)
          SELECT 152,24,1,'uidProduct',1,1,1
UNION ALL SELECT 153,24,2,'productName',2,0,1
UNION ALL SELECT 154,24,3,'description',2,0,1
UNION ALL SELECT 155,24,4,'uidAccountIncome',1,0,1
UNION ALL SELECT 156,24,5,'uidAccountAsset',1,0,1
UNION ALL SELECT 157,24,6,'uidAccountExpense',1,0,1
UNION ALL SELECT 158,24,7,'isActive',1,0,1
UNION ALL SELECT 159,24,8,'isContainer',1,0,1
UNION ALL SELECT 160,24,9,'openingQty',4,0,1
UNION ALL SELECT 161,24,10,'openingValuation',1,0,1
UNION ALL SELECT 162,24,11,'journalQty',4,0,0
UNION ALL SELECT 163,24,12,'journalValuation',1,0,0
UNION ALL SELECT 180,24,13,'learnPrices',1,0,0

UNION ALL SELECT 164,25,1,'uidBom',1,1,1
UNION ALL SELECT 165,25,2,'uidProduct',1,0,1
UNION ALL SELECT 166,25,3,'uidProductPart',1,0,1
UNION ALL SELECT 167,25,4,'qty',4,0,1

UNION ALL SELECT 171,18,4,'relationType',1,0,1

UNION ALL SELECT 172,2,9,'uidProduct',1,0,1
UNION ALL SELECT 173,2,10,'qty',4,0,1
UNION ALL SELECT 174,2,11,'assetDirection',1,0,1
UNION ALL SELECT 175,2,12,'costAmount',1,0,1

UNION ALL SELECT 176,1,10,'direction',1,0,1

UNION ALL SELECT 177,21,9,'uidProduct',1,0,1
UNION ALL SELECT 178,21,10,'qty',4,0,1

UNION ALL SELECT 181,27,1,'uidProductPrice',1,1,1
UNION ALL SELECT 182,27,2,'uidProduct',1,0,1
UNION ALL SELECT 183,27,3,'qty',4,0,1
UNION ALL SELECT 184,27,4,'each',4,0,1
UNION ALL SELECT 185,27,5,'learned',1,0,1;

-- Now if the an account is deleted, remove the products so they're just 'not set'
DROP TRIGGER tr_account_del;

CREATE TRIGGER tr_account_del AFTER DELETE ON account FOR EACH ROW
BEGIN
  delete from accountDetail where uidAccount = old.uidAccount;
  delete from statement where uidAccount = old.uidAccount;
  delete from budget where uidAccount = old.uidAccount;
  delete from workerCustomerRel where uidAccount = old.uidAccount;
  delete from journal where uidJournal in (select jl.uidJournal
                                             from journalLine jl
                                            inner join journalLineDetail jld
                                               on jld.uidJournalLine = jl.uidJournalLine
                                            where jld.uidAccount = old.uidAccount );
  delete from journal where uidJournal in (select jl.uidJournal
                                             from journalLine jl
                                            where jl.uidAccountVat = old.uidAccount );
  delete from estimate where uidEstimate in (select el.uidEstimate
                                               from estimateLine el
                                              inner join estimateLineDetail eld
                                                 on eld.uidEstimateLine = el.uidEstimateLine
                                              where eld.uidAccount = old.uidAccount );
  delete from estimate where uidEstimate in (select el.uidEstimate
                                               from estimateLine el
                                              where el.uidAccountVat = old.uidAccount );
  update product set uidAccountAsset = 0 where uidAccountAsset = old.uidAccount;
  update product set uidAccountIncome = 0 where uidAccountIncome = old.uidAccount;
  update product set uidAccountExpense = 0 where uidAccountExpense = old.uidAccount;
END;

DROP TRIGGER tr_journal_del;

CREATE TRIGGER tr_journal_del1 BEFORE DELETE ON journal FOR EACH ROW
BEGIN
  delete from journal
   where uidJournal in (select uidJournalTransfer
                          from payment
                         where uidJournalInvoice = old.uidJournal
                           and relationType = 3);
END;

CREATE TRIGGER tr_journal_del2 AFTER DELETE ON journal FOR EACH ROW
BEGIN
  delete from journalLine where uidJournal = old.uidJournal;
  delete from statementLine where uidJournal = old.uidJournal;
  delete from timeEntry where uidJournal = old.uidJournal and isInvoice=1;
  update timeEntry set uidJournal = 0 where uidJournal = old.uidJournal;
  delete from payment where uidJournalInvoice = old.uidJournal;
  delete from payment where uidJournalTransfer = old.uidJournal;
  update estimate set uidJournal = 0 where uidJournal = old.uidJournal;
END;

DROP TRIGGER tr_payment_upd;
DROP TRIGGER tr_payment_ins;
DROP TRIGGER tr_payment_del;

CREATE TRIGGER tr_payment_upd UPDATE ON payment 
WHEN (new.uidJournalInvoice <> old.uidJournalInvoice)
BEGIN
  update journal set isPaid = isPaid - 1
   where uidJournal = old.uidJournalInvoice
     and old.relationType = 1
     and new.uidJournalInvoice <> old.uidJournalInvoice;

  update journal set isPaid = isPaid + 1
   where uidJournal = new.uidJournalInvoice
     and new.relationType = 1
     and new.uidJournalInvoice <> old.uidJournalInvoice;
END;

CREATE TRIGGER tr_payment_ins INSERT ON payment 
BEGIN
  update journal set isPaid = isPaid + 1
   where uidJournal = new.uidJournalInvoice
     and new.relationType = 1;
END;

CREATE TRIGGER tr_payment_del DELETE ON payment 
BEGIN
  update journal set isPaid = isPaid - 1
   where uidJournal = old.uidJournalInvoice
     and old.relationType = 1;
END;

CREATE TRIGGER tr_product_del AFTER DELETE ON product FOR EACH ROW
BEGIN
  delete from productPrice where uidProduct = old.uidProduct;
END;

-- constraint triggers...
CREATE TRIGGER tr_product_c_ins BEFORE INSERT ON product FOR EACH ROW
BEGIN
  select case
    when not exists (select uidAccount from account where uidAccount = new.uidAccountAsset) and new.uidAccountAsset <> 0
    then raise (abort, 'Asset account does not exist. FK:product:uidAccountAsset')
  end;
  select case
    when not exists (select uidAccount from account where uidAccount = new.uidAccountIncome) and new.uidAccountIncome <> 0
    then raise (abort, 'Income account does not exist. FK:product:uidAccountIncome')
  end;
  select case
    when not exists (select uidAccount from account where uidAccount = new.uidAccountExpense) and new.uidAccountExpense <> 0
    then raise (abort, 'Expense account does not exist. FK:product:uidAccountExpense')
  end;
END;

CREATE TRIGGER tr_bom_c_ins BEFORE INSERT ON bom FOR EACH ROW
BEGIN
  select case
    when not exists (select uidProduct from product where uidProduct = new.uidProduct)
    then raise (abort, 'Product does not exist. FK:product:uidProduct')
  end;
  select case
    when not exists (select uidProduct from product where uidProduct = new.uidProductPart)
    then raise (abort, 'Product does not exist. FK:product:uidProductPart')
  end;
END;

DROP TRIGGER tr_journalLine_c_ins;

CREATE TRIGGER tr_journalLine_c_ins BEFORE INSERT ON journalLine FOR EACH ROW
BEGIN
  select case
    when not exists (select uidAccount from account where uidAccount = new.uidAccountVat) and new.uidAccountVat <> 0
    then raise (abort, 'VAT account does not exist. FK:journalLine:uidAccountVat')
  end;

  select case
    when not exists (select uidJournal from journal where uidJournal = new.uidJournal)
    then raise (abort, 'Transaction does not exist. FK:journalLine:uidJournal')
  end;

  select case
    when not exists (select uidProduct from product where uidProduct = new.uidProduct) and new.uidProduct <> 0
    then raise (abort, 'Product does not exist. FK:journalLine:uidProduct')
  end;
END;

-- Triggers to maintain the qty and valuation of products
drop trigger tr_journalLine_upd;
drop trigger tr_journalLine_ins;
drop trigger tr_journalLine_del;

-- Re-create the triggers...
CREATE TRIGGER tr_journalLine_upd UPDATE ON journalLine 
WHEN (old.amount <> new.amount or 
      old.uidAccountVat <> new.uidAccountVat or 
      old.vatAmount <> new.vatAmount or 
      old.vatDirection <> new.vatDirection or
      old.uidProduct <> new.uidProduct or
      old.qty <> new.qty or
      old.assetDirection <> new.assetDirection)
BEGIN
  -- Update the account balances, and the vat account balances.
  -- First, from the 'from' account, remove the old balance (ex vat)
  update account set journalBalance = journalBalance + (old.amount - new.amount) 
   where uidAccount = (select uidAccount 
                         from journalLineDetail 
                        where direction = -1
                          and uidJournalLine = old.uidJournalLine);

  -- Next, remove any vat that might be in the balance using the optional flag addVatToBalance...
  update account set journalBalance = journalBalance + (old.vatAmount - new.vatAmount)
   where uidAccount = (select uidAccount 
                         from journalLineDetail 
                        where direction = -1
                          and uidJournalLine = old.uidJournalLine)
     and uidAccountSubCat in (select uidAccountSubCat
                                from accountSubCategory
                               where addVatToBalance = 1);

  -- From the 'to' account, remove the old balance (ex vat)
  update account set journalBalance = journalBalance + (new.amount - old.amount) 
   where uidAccount = (select uidAccount 
                         from journalLineDetail 
                        where direction = 1
                          and uidJournalLine = new.uidJournalLine);

  -- Next, remove any vat that might be in the balance using the optional flag addVatToBalance...
  update account set journalBalance = journalBalance + (new.vatAmount - old.vatAmount)
   where uidAccount = (select uidAccount
                         from journalLineDetail 
                        where direction = 1
                          and uidJournalLine = new.uidJournalLine)
     and uidAccountSubCat in (select uidAccountSubCat
                               from accountSubCategory
                              where addVatToBalance = 1);

  -- Now we need to update the vat accounts. The uidAccountVat column links the vatAmount to a vat account.
  -- We remove the vat total from the old account and add it to the new.
  update account set journalBalance = journalBalance - old.vatAmount * old.vatDirection
                                                     * (select scaleBalance 
                                                          from accountSubCategory
                                                         where uidAccountSubCat = (select uidAccountSubCat
                                                                                     from account
                                                                                    where uidAccount = old.uidAccountVat))
   where uidAccount = old.uidAccountVat;

  update account set journalBalance = journalBalance + new.vatAmount * new.vatDirection
                                                     * (select scaleBalance 
                                                          from accountSubCategory
                                                         where uidAccountSubCat = (select uidAccountSubCat
                                                                                     from account
                                                                                    where uidAccount = new.uidAccountVat))
   where uidAccount = new.uidAccountVat;
   
  update product
     set journalQty = journalQty - old.qty * old.assetDirection + new.qty * new.assetDirection,
         journalValuation = journalValuation 
                            - (abs(old.amount) * old.assetDirection)
                            + (abs(new.amount) * new.assetDirection)
   where uidProduct = old.uidProduct
     and old.uidProduct = new.uidProduct
     and old.uidProduct <> 0;

  update product
     set journalQty = journalQty - old.qty * old.assetDirection,
         journalValuation = journalValuation - (abs(old.amount) * old.assetDirection)
   where uidProduct = old.uidProduct
     and old.uidProduct <> new.uidProduct
     and old.uidProduct <> 0;

  update product
     set journalQty = journalQty + new.qty * new.assetDirection,
         journalValuation = journalValuation + (abs(new.amount) * new.assetDirection)
   where uidProduct = new.uidProduct
     and old.uidProduct <> new.uidProduct
     and new.uidProduct <> 0;
END;

CREATE TRIGGER tr_journalLine_ins INSERT ON journalLine 
BEGIN
  update account set journalBalance = journalBalance + new.vatAmount * new.vatDirection
                                                     * (select scaleBalance 
                                                          from accountSubCategory
                                                         where uidAccountSubCat = (select uidAccountSubCat
                                                                                     from account
                                                                                    where uidAccount = new.uidAccountVat))
   where uidAccount = new.uidAccountVat;

  -- Update product qty
  update product
     set journalQty = journalQty + new.qty * new.assetDirection,
         journalValuation = journalValuation + (abs(new.amount) * new.assetDirection)
   where uidProduct = new.uidProduct
     and new.uidProduct <> 0;
END;

CREATE TRIGGER tr_journalLine_del DELETE ON journalLine 
BEGIN
  -- We update the VAT balance here (only), then delete the foreign key lines (journalLineDetail)
  -- Doing this will adjust the balances of the accounts.
  update account set journalBalance = journalBalance - old.vatAmount * old.vatDirection
                                                     * (select scaleBalance 
                                                          from accountSubCategory
                                                         where uidAccountSubCat = (select uidAccountSubCat
                                                                                     from account
                                                                                    where uidAccount = old.uidAccountVat))
   where uidAccount = old.uidAccountVat;

  -- Update product qty
  update product
     set journalQty = journalQty - old.qty * old.assetDirection,
         journalValuation = journalValuation - (abs(old.amount) * old.assetDirection)
   where uidProduct = old.uidProduct
     and old.uidProduct <> 0;

  -- Now delete the journal line detail records
  delete from journalLineDetail where uidJournalLine = old.uidJournalLine;
END;

-- Update user data
create temporary table z_tmp (
  uidJournal int not null primary key,
  direction int not null
);

create temporary table z_refs (
  uidJournal int not null,
  uidAccount int not null,
  direction int not null,
  refCount int not null
);

insert into z_refs (uidJournal, uidAccount, direction, refCount)
select j.uidJournal,
       jld.uidAccount,
       jld.direction,
       count(*) as refCount
  from journal j
 inner join journalLine jl 
    on jl.uidJournal = j.uidJournal 
 inner join journalLineDetail jld 
    on jld.uidJournalLine = jl.uidJournalLine 
 group by j.uidJournal, 
          jld.uidAccount,
          jld.direction;

insert into z_tmp (uidJournal, direction)
select z.uidJournal,
       z.direction
  from z_refs z
 inner join (select uidJournal,
                    max(refCount) as refCount
               from z_refs
              group by uidJournal
             having min(refCount) <> max(refCount)) multi
    on multi.uidJournal = z.uidJournal
   and multi.refCount = z.refCount;

insert into z_tmp (uidJournal, direction)
select j.uidJournal,
       min(jld.direction)
  from journal j
 inner join journalLine jl 
    on jl.uidJournal = j.uidJournal 
 inner join journalLineDetail jld 
    on jld.uidJournalLine = jl.uidJournalLine 
 inner join account a 
    on a.uidAccount = jld.uidAccount 
 inner join accountSubCategory sc
    on sc.uidAccountSubCat = a.uidAccountSubCat
 where j.uidJournalType = 3
   and sc.accountSubCatID = 'CUSTOMER'
   and not exists (select null
                     from z_tmp
                    where uidJournal = j.uidJournal)
 group by j.uidJournal;

insert into z_tmp (uidJournal, direction)
select j.uidJournal,
       max(jld.direction)
  from journal j
 inner join journalLine jl 
    on jl.uidJournal = j.uidJournal 
 inner join journalLineDetail jld 
    on jld.uidJournalLine = jl.uidJournalLine 
 inner join account a 
    on a.uidAccount = jld.uidAccount 
 inner join accountSubCategory sc
    on sc.uidAccountSubCat = a.uidAccountSubCat
 where j.uidJournalType = 2
   and sc.accountSubCatID = 'SUPPLIER'
   and not exists (select null
                     from z_tmp
                    where uidJournal = j.uidJournal)
 group by j.uidJournal;
 
update journal
   set direction = (select direction 
                      from z_tmp 
                     where z_tmp.uidJournal = journal.uidJournal)
 where exists (select null
                 from z_tmp
                where z_tmp.uidJournal = journal.uidJournal);

update journal
   set direction = 1
 where direction = 0;
 
-- Update the schema
update systemSetting
   set settingValue = 36
 where uidSetting = 5;